﻿using System;
using System.Collections.Generic;
using System.Text;
using QuanLyNhaSach.QuanLyNhaSachDTO;
using System.Data;

namespace QuanLyNhaSach.QuanLyNhaSachDao
{
    class PhieuNhapChiTietDao
    {
        public static DataTable GetChiTietPhieuNhapAll()
        {
            string sql = "select * from ChiTietPhieuNhap where Active=True";
            return Database.ExcuQuery(sql);
        }
        public static DataTable GetChiTietPhieuNhapTheompn(int mapn)
        {
            string sql = "select* from ChiTietPhieuNhap where Active=True and MaPhieuNhap=" + mapn + "";
            return Database.ExcuQuery(sql);
        }
        public static bool Insert(ChiTietPhieuNhapDTO pn)
        {
            string sql;
            bool active = true;


            sql = "insert into ChiTietPhieuNhap(MaSach,MaPhieuNhap,SoLuongNhap,Active) values('" + pn.MaSach + "','" + pn.MaPhieuNhap + "'," + pn.SoLuongNhap + "," + active + ") ";

            Database.ExcuNonQuery(sql);
            return true;

        }
        public static void InsertBaoCaoTon(ChiTietPhieuNhapDTO pn)
        {
            string sql;
            bool active = true;

            sql = "select* from Sach where Active=True and MaSach=" + Convert.ToInt32(pn.MaSach) + "";
            DataTable dt= Database.ExcuQuery(sql);
            int tonht=0;
           if (dt.Rows.Count>0)
                tonht=Convert.ToInt32(dt.Rows[0]["SoLuongTon"].ToString());
            //int tondau = pn.SoLuongNhap - tonht;
           sql = "select* from PhieuNhapSach where Active=True and MaPhieuNhap=" + Convert.ToInt32(pn.MaPhieuNhap) + "";
           dt = Database.ExcuQuery(sql);
           DateTime t = DateTime.Now;
           if (dt.Rows.Count > 0)
               t = Convert.ToDateTime(dt.Rows[0]["NgayNhap"].ToString());
           
                sql = "insert into BaoCaoTon(MaSach,TonDau,PhatSinh,TonCuoi,Active,NgayPhatSinh) values('" + pn.MaSach + "'," + (tonht-pn.SoLuongNhap) + "," + 0 + "," + tonht + "," + active+",'" + t + "') ";

            Database.ExcuNonQuery(sql);
           

        }
        public static void UpdateSoLuongSach(ChiTietPhieuNhapDTO pn)
        {
            string sql = "Update  Sach set SoLuongTon= SoLuongTon + (" + pn.SoLuongNhap + ") where MaSach = " + pn.MaSach + "";

            Database.ExcuNonQuery(sql);

        }
        public static bool Delete(ChiTietPhieuNhapDTO pn)
        {

            string sql = "delete from ChiTietPhieuNhap where MaChiTietPhieuNhap=" + pn.MaChiTietPhieuNhap + "";
            Database.ExcuNonQuery(sql);
            return true;
        }
        public static void DeleteTheoMaPhieuNhap(string pn)
        {

            string sql = "delete from ChiTietPhieuNhap where MaPhieuNhap='" + pn + "'";
            Database.ExcuNonQuery(sql);
            

        }
        public static bool Update(ChiTietPhieuNhapDTO pn)
        {
            string sql = "Update  ChiTietPhieuNhap set MaSach= ('" + pn.MaSach + "'),MaPhieuNhap= ('" + pn.MaPhieuNhap + "'),SoLuongNhap= ('" + pn.SoLuongNhap + "') where MaChiTietPhieuNhap = " + pn.MaChiTietPhieuNhap + "";

            Database.ExcuNonQuery(sql);
            return true;
        }
        public static bool KTSoLuongTon(ChiTietPhieuNhapDTO pn)
        {

            string sql = "select SoLuongTon from Sach where MaSach=" + pn.MaSach + "";
            DataTable dt = Database.ExcuQuery(sql);
            int e = Convert.ToInt32(dt.Rows[0]["SoLuongTon"].ToString());
            if (e < 300)
                return true;
            return false;
        }
        public static void UpdateXoa(ChiTietPhieuNhapDTO pn)
        {
            string sql = "Update  Sach set SoLuongTon= SoLuongTon - (" + pn.SoLuongNhap + ") where MaSach = " + pn.MaSach + "";

            Database.ExcuNonQuery(sql);
        }
        public static int SoLuongNhap(ChiTietPhieuNhapDTO pn)
        {

            string sql = "select SoLuongNhap from ChiTietPhieuNhap where MaChiTietPhieuNhap=" + pn.MaChiTietPhieuNhap + "";
            DataTable dt = Database.ExcuQuery(sql);
            int e = Convert.ToInt32(dt.Rows[0]["SoLuongNhap"].ToString());
            return e;
        }
        public static void UpdateSua(int sl, ChiTietPhieuNhapDTO s)
        {
            string sql = "update Sach set SoLuongTon  =( " + sl + ") where MaSach = " + s.MaSach+ "";
            Database.ExcuNonQuery(sql);
        }
        public static int SoLuongTon(ChiTietPhieuNhapDTO pn)
        {

            string sql = "select SoLuongTon from Sach where MaSach=" + pn.MaSach + "";
            DataTable dt = Database.ExcuQuery(sql);
            int e = Convert.ToInt32(dt.Rows[0]["SoLuongTon"].ToString());
            return e;
        }
    } 
}
